We constructed a two-leveled report on the basis of the data from two tables in the example above. FastReport allows constructing analogous reports on the basis of one set of data, formed in a unique way.
To perform this, one needs to create a query using SQL language, which would return data, arranged according to a certain condition, from both of the tables. In our case, a condition is a correspondence of the "CustNo" fields in both of the tables. An SQLquery may look as follows:
select * from customer, orders
where orders.CustNo = customer.CustNo
order by customer.CustNo
The "order by" line is necessary for sorting the records in the "CustNo" field. The example below shows how the query data would be returned:
| CustNo | Company … | OrderNo | SaleDate |
| 1221 | Kauai Dive Shoppe | 1023 | 01.07.1988 |
| 1221 | Kauai Dive Shoppe | 1123 | 24.08.1993 |
| 1231 | Unisco | 1060 | 28.02.1989 |
| 1351 | Sight Diver | 1003 | 12.04.1988 |
| 1351 | Sight Diver | 1052 | 06.01.1989 |
| 1351 | Sight Diver | 1055 | 04.02.1989 |
How can a multi-leveled report be constructed on the basis of this data? In FastReport there is a special band – "Group Header". A special condition is established for the band (DB field value or an expression); the band is displayed as soon as the field's value is changed. The following example illustrates this.
Let us create a new project in Delphi, put the "TQuery," "TfrxReport," and "TfrxDBDataSet" components on the form. Let us set them in the following way:
Query1:
DatabaseName = 'DBDEMOS'
SQL =
select * from customer, orders
where orders.CustNo = customer.CustNo
order by customer.CustNo
frxDBDataSet1:
DataSet = Query1
UserName = 'Group'
Let us open the designer and connect our data source to the report. After that, add the "Group header" and "Master data" bands to the report. Set a condition (in this case, it is "Group.CustNo" data field) in the "Group header" band editor:
Let us link data-band to the "Group" data source and place the objects in the following way (note, that the group header must be allocated above the data-band):
Let us link data-band to the "Group" data source and place the objects in the following way (note, that the group header must be allocated above the data-band):
On starting, we would get a report similar to the one shown below:
As you can see, the "Group header" band is displayed only when the field, to which it is connected, changes its value. Otherwise, the data-band connected to the group is displayed. If compare this report to the master-detail report, which we constructed earlier, it seems to be obvious that order numbers are not sorted in ascending order. It can be easily corrected by changing the SQL query text:
select * from customer, orders
where orders.CustNo = customer.CustNo
order by customer.CustNo, orders.OrderNo
Similarly, the reports with nested group can be constructed. At the same time, the number of enclosures is unlimited in such reports. Thus, the reports with groups have some advantages over the reports of the master-detail type:
- the whole report needs only one table (query);
- the number of the data enclosuring levels is unlimited;
- the additional data sorting feature;
- more rational usage of the DB resources (the query returns only the data, which should be printed, without having to filtrate the data).
The only disadvantage is the necessity of writing queries in SQL language. However, knowledge of SQL basis is obligatory for a programmer working with databases.